﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PetaPoco;
using Shop.Wares.Models;

namespace Shop.Wares.Repositories
{
    public class WaresAccess
    {
        private Database DB = new Database("ConnString");

        #region 类别与子类别管理
        /// <summary>
        /// 验证类别名是否存在
        /// </summary>
        /// <param name="CName">类别名</param>
        /// <returns></returns>
        public WaresCategory VerificationCName(string CName)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_WaresCategory where CName = @0",CName);
            return DB.FirstOrDefault<WaresCategory>(sql);
        }
        /// <summary>
        /// 添加类别
        /// </summary>
        /// <param name="winfo">类别信息</param>
        /// <returns></returns>
        public object AddWaresCategory(WaresCategory winfo)
        {
            return DB.Insert("Shop_WaresCategory", "CID", winfo);
        }
        /// <summary>
        /// 获取类别信息
        /// </summary>
        /// <returns></returns>
        public IList<WaresCategory> GetWinfo()
        {
            Sql sql = Sql.Builder.Append("select * from Shop_WaresCategory order by OrderTime desc");
            return DB.Fetch<WaresCategory>(sql);
        }

        /// <summary>
        /// 获取销量总数
        /// </summary>
        /// <returns></returns>
        public int GetCount()
        {
            string sqlfy = "select WID,WName,PPrice,SaleNum,Stock from Shop_Wares";

            Sql sql = Sql.Builder.Append(sqlfy);
            return DB.Fetch<WaresSale>(sql).Count;
        }

        /// <summary>
        /// 分页查询电子书的销售情况
        /// </summary>
        /// <param name="value"></param>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public List<WaresSale> GetWaresSale
            (string value = "",int page=0,int limit = 0)
        {
            string sqlfy = "SELECT TOP " + limit
                + "WID,WName,PPrice,SaleNum,Stock,RowNumber " +
                "FROM(SELECT ROW_NUMBER() OVER(ORDER BY WID)" +
                " AS RowNumber, WID,WName,PPrice,SaleNum,Stock" +
                " FROM Shop_Wares where  WID like '%" + value + 
                "%' or WName like '%" + value + "%') as A WHERE " +
                "RowNumber > " + limit + " * (" + page + " - 1) "; 
            Sql sql = Sql.Builder.Append(sqlfy);
            return DB.Fetch<WaresSale>(sql);
        }
        /// <summary>
        /// 删除类别
        /// </summary>
        /// <param name="CID">类别ID</param>
        /// <returns></returns>
        public int DelWaresCategory(int CID)
        {
            Sql sql = Sql.Builder.Append("delete from Shop_WaresCategory where CID = @0",CID);
            return DB.Execute(sql);
        }
        /// <summary>
        /// 修改类别
        /// </summary>
        /// <param name="CID">l类别ID</param>
        /// <returns></returns>
        public object UpdateWaresCategory(WaresCategory winfo)
        {
            Sql sql = Sql.Builder.Append("update Shop_WaresCategory set CName = @0 ,Describe = @1 where CID = @2", winfo.CName,winfo.Describe,winfo.CID);
            return DB.Execute(sql);
            //return DB.Update("Shop_WaresCategory", "CID", winfo);
        }
        /// <summary>
        /// 根据类别ID获取该类别下子类别信息
        /// </summary>
        /// <param name="CID">类别ID</param>
        /// <returns></returns>
        public IList<SonWaresCategory> GetSWinfo(int CID)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_SonWaresCategory where CID = @0  order by OrderTime desc", CID);
            return DB.Fetch<SonWaresCategory>(sql);
        }
        /// <summary>
        /// 置顶类别
        /// </summary>
        /// <param name="CID">类别ID</param>
        /// <returns></returns>
        public int OrderTimeWaresCategory(int CID)
        {
            Sql sql = Sql.Builder.Append("update Shop_WaresCategory set OrderTime = getdate() where CID = @0",CID);
            return DB.Execute(sql);
        }








        /// <summary>
        /// 验证子类别名是否存在
        /// </summary>
        /// <param name="SCName">类别名</param>
        /// <returns></returns>
        public SonWaresCategory VerificationSCName(string SCName)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_SonWaresCategory where SCName = @0", SCName);
            return DB.FirstOrDefault<SonWaresCategory>(sql);
        }
        /// <summary>
        /// 添加子类别
        /// </summary>
        /// <param name="swinfo">子类别信息</param>
        /// <returns></returns>
        public object AddSonWaresCategory(SonWaresCategory swinfo)
        {
            return DB.Insert("Shop_SonWaresCategory", "SCID", swinfo);
        }
        /// <summary>
        /// 删除子类别
        /// </summary>
        /// <param name="SCID">子类别ID</param>
        /// <returns></returns>
        public int DelSonWaresCategory(int SCID)
        {
            Sql sql = Sql.Builder.Append("delete from Shop_SonWaresCategory where SCID = @0", SCID);
            return DB.Execute(sql);
        }
        /// <summary>
        /// 修改子类别
        /// </summary>
        /// <param name="SCID">子类别ID</param>
        /// <returns></returns>
        public object UpdateSonWaresCategory(SonWaresCategory swinfo)
        {
            Sql sql = Sql.Builder.Append("update Shop_SonWaresCategory set SCName = @0 ,Describe = @1 where SCID = @2", swinfo.SCName, swinfo.Describe, swinfo.SCID);
            return DB.Execute(sql);
        }
        /// <summary>
        /// 置顶子类别
        /// </summary>
        /// <param name="SCID">子类别ID</param>
        /// <returns></returns>
        public int OrderTimeSonWaresCategory(int SCID)
        {
            Sql sql = Sql.Builder.Append("update Shop_SonWaresCategory set OrderTime = getdate() where SCID = @0", SCID);
            return DB.Execute(sql);
        }
        #endregion

        #region 商品管理
        /// <summary>
        ///分页获取商品信息 
        /// </summary>
        /// <param name="page">第几页</param>
        /// <param name="itemsPerPage">每页多少行</param>
        /// <returns></returns>
        public Page<Models.AnyWares> GetWaresInfo(long page, long itemsPerPage)
        {
            Sql sql = Sql.Builder.Append("select Shop_Wares.*,Shop_WaresCategory.CName,Shop_SonWaresCategory.SCName from Shop_Wares join Shop_WaresCategory on Shop_Wares.CID = Shop_WaresCategory.CID join Shop_SonWaresCategory on Shop_Wares.SCID = Shop_SonWaresCategory.SCID order by Shop_Wares.OrderTime desc");
            return DB.Page<Models.AnyWares>(page, itemsPerPage, sql);
        }
        /// <summary>
        /// 添加商品
        /// </summary>
        /// <param name="winfo"></param>
        /// <returns></returns>
        public object AddWares(Models.Wares winfo)
        {
            return DB.Insert("Shop_Wares","WID", winfo);
        }
        /// <summary>
        /// 根据商品ID获取商品信息
        /// </summary>
        /// <param name="WID"></param>
        /// <returns></returns>
        public Models.Wares GetWaresInfo(int WID)
        {
            Sql sql = Sql.Builder.Append("select * from Shop_Wares where WID = @0",WID);
            return DB.First<Models.Wares>(sql);
        }
        /// <summary>
        /// 修改商品信息
        /// </summary>
        /// <param name="winfo"></param>
        /// <returns></returns>
        public int UpdateWares(Models.Wares winfo)
        {
            Sql sql = Sql.Builder.Append("update Shop_Wares set WName = @0,CID = @1,SCID =@2 ,OPrice= @3,PPrice= @4,Stock =@5 ,Describe= @6,Introduce =@7,Url = @8 where WID = @9",winfo.WName,winfo.CID,winfo.SCID,winfo.OPrice,winfo.PPrice,winfo.Stock,winfo.Describe,winfo.Introduce,winfo.Url,winfo.WID);
            return DB.Execute(sql);
        }
        /// <summary>
        /// 置顶商品
        /// </summary>
        /// <param name="WID">商品ID</param>
        /// <returns></returns>
        public int OrderTimeWares(int WID)
        {
            Sql sql = Sql.Builder.Append("update Shop_Wares set OrderTime = getdate() where WID = @0", WID);
            return DB.Execute(sql);
        }
        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="WID"></param>
        /// <returns></returns>
        public int DelWares(int WID)
        {
            Sql sql = Sql.Builder.Append("delete from Shop_Wares where WID = @0", WID);
            return DB.Execute(sql);
        }

        /// <summary>
        ///分页获取搜索的商品信息 
        /// </summary>
        /// <param name="page">第几页</param>
        /// <param name="itemsPerPage">每页多少行</param>
        /// <param name="CID">类别ID</param>
        /// <param name="SCID">子类别Id</param>
        /// <param name="WName">商品名</param>
        /// <param name="Status">商品状态</param>
        /// <returns></returns>
        public Page<Models.AnyWares> GetSerchWaresInfo(long page, long itemsPerPage,int CID,int SCID, string WName,int Status)
        {
            string strsql = string.Format("select Shop_Wares.*,Shop_WaresCategory.CName,Shop_SonWaresCategory.SCName from Shop_Wares join Shop_WaresCategory on Shop_Wares.CID = Shop_WaresCategory.CID join Shop_SonWaresCategory on Shop_Wares.SCID = Shop_SonWaresCategory.SCID where Shop_Wares.WName like '%{0}%'",WName);
            if (CID != 0)
            {
                strsql += string.Format("and Shop_Wares.CID = {0}", CID);
            }
            if (SCID != 0)
            {
                strsql += string.Format("and Shop_Wares.SCID = {0} ", SCID);
            }
            strsql += string.Format("and Shop_Wares.Status = {0}", Status);

            Sql sql = Sql.Builder.Append(strsql+ " order by Shop_Wares.OrderTime desc");
            return DB.Page<Models.AnyWares>(page, itemsPerPage, sql);
        }
        /// <summary>
        /// 上下架0上架1下架
        /// </summary>
        /// <param name="Type"></param>
        /// <param name="WID"></param>
        /// <returns></returns>
        public int UDFrame(int Type,int WID)
        {
            Sql sql = Sql.Builder.Append("update Shop_Wares set Status = @0 where WID = @1", Type, WID);
            return DB.Execute(sql);
        }
        #endregion
    }
}